﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetAllStaff]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetAllStaff];
GO
CREATE PROCEDURE [dbo].[sproc_GetAllStaff]
    @StaffType bit = 0
/*

====================================================
功能:    得到所有的人员
参数:
    @StaffType bit            :    员工类型
    
====================================================

*/
AS
BEGIN
SET NOCOUNT ON

SELECT
    a.*,
    (
        CASE
            WHEN a.birthday is NULL then '-'
            WHEN datediff(dd, a.birthday, '1900-01-01')=0 then '-'
            ELSE convert(nvarchar(64), datediff(yy, a.birthday, getdate()))
        END
    ) AS Age,
    (CASE sex WHEN 1 then N'男' ELSE N'女' END) AS SexName,
    Convert(nvarchar(10),A.RegistedDate,120) AS RQ,
    (SELECT Position_name FROM dbo.uds_Position WHERE Position_id = b.Position_id) AS Position_Name
FROM
    dbo.uds_staff a,
    dbo.uds_staff_in_position b
WHERE
    Dimission = @StaffType
    and a.staff_id = b.staff_id
ORDER BY
    A.RegistedDate DESC

END
GO